﻿if OBJECT_ID('spGetFactCost','P') is not null
        drop proc spGetFactCost
go
create proc spGetFactCost @branch_code varchar(10)=null,@cost_code varchar(50)=null,@timekey int=null,@unit_code varchar(10)=null
as
        DECLARE  @query Nvarchar(max)
        DECLARE  @where Nvarchar(max)
        set @query=N'SELECT f.*,b.name as branch_name,d.name as criteria_name,u.name as unit_name FROM dbo.dim_costs_revenues d join dbo.fact_costs_revenues f on (d.code=f.cost_code and d.unit_code=f.unit_code) join dbo.dim_unit u on d.unit_code=u.code right join VNPT_common.dbo.branch b on b.code=f.branch_code'
        set @where=N'd.name <> N''Trong đó: thu ngoại tệ (USD)'''
        if(@branch_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'branch_code=N'''+@branch_code+''''
        end
        if(@cost_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'cost_code=N'''+@cost_code+''''
        end
        if(@timekey is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+'timekey='+cast(@timekey as varchar(10))
        end
        if(@unit_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'unit_code=N'''+@unit_code+''''
        end
        if(@where<>'')
                set @query=@query+N' where '+@where     
        --print @query
        exec (@query)
        exec spGetFactCost
        
SELECT f.cost,f.timekey,d.code,d.name as crit,u.name as unit 
FROM dbo.dim_costs_revenues d left join dbo.fact_costs_revenues f 
        on (d.code=f.cost_code and d.unit_code=f.unit_code) left join dbo.dim_unit u on d.unit_code=u.code 
where d.name <>N'Trong đó: thu ngoại tệ (USD)'

SELECT f.cost,f.timekey,d.code,d.name as crit,u.name as unit FROM dbo.dim_costs_revenues d left join dbo.fact_costs_revenues f on (d.code=f.cost_code and d.unit_code=f.unit_code) left join dbo.dim_unit u on d.unit_code=u.code where d.name <> N'Trong dó: thu ngo?i t? (USD)' and d.code like 'R.%'

select * from VNPT_common.dbo.branch

SELECT * FROM dbo.fact_costs_revenues
pivot (max(cost) for branch_code in (select code from VNPT_common.dbo.branch)) as cost

